Indexed tables

Creating indexes for database tables can increase the speed of data access and reduce the time it takes for the program to evaluate data. Some DBMS applications automatically index your database tables, while others require that you create an index yourself. For the best report generation performance, make sure each of your database tables has a corresponding index.

Note:    Some DBMS applications do not support indexed tables. Refer to the documentation for your DBMS to find out if it supports indexes and how to create them. If your DBMS documentation does not mention indexed tables, it may not support them, and you should link tables based on common fields. The Visual Linking Expert can also help you determine if your tables include indexes.

Indexes organize the records in a relational database table so that data can be located easier. For example, assume you have a table with the following data:

Order# Customer Amount

10444

Allez Distribution

25141.50

10470

BG Mountain Inc.

19164.30

10485

Sierra Mountain

8233.50

10488

Mountain Toad

24580.50

10495

SFB Inc.

7911.80

10501

La Bomba de Bicicleta

1956.20

10511

BG Mountain Inc.

1683.60

10544

Sierra Bicycle Group

19766.20

10568

Mountain Tops Inc.

29759.55

10579

Sierra Bicycle Group

12763.95

The information in this table is organized according to the Order# field. This is fine anytime you want to look up information in the table based on order numbers. However, what if you want to look up information specific to a certain customer?

Say you want to look up all orders made by Sierra Bicycle Group. The database engine must begin by looking at the first order number in the list and checking to see if the customer name matches the request. If not, it goes to the second order number, and checks that customer name. When an order number is reached that contains the correct customer name, the database engine retrieves the information, then continues to the next order number. Using this technique, both the Order# field and the Customer field must be read for every single record in the table. This takes a long time and a large amount of computer processing effort for examining extensive database tables with thousands, or even millions of records.

Instead, you can create an index for the table based on the Customer field. Such an index might look like this:

Customer Pointer to Order#

Allez Distribution

10444

BG Mountain Inc.

10470

BG Mountain Inc.

10511

La Bomba de Bicicleta

10501

Mountain Toad

10488

Mountain Tops Inc.

10568

SFB Inc.

10495

Sierra Bicycle Group

10544

Sierra Bicycle Group

10579

Sierra Mountain

10485

In this index, information is organized by customers, not order numbers. Also, notice that the second column actually contains pointers to specific order numbers in the original table. By using this index, the database engine can search just the information in the Customer column until it finds the customer you are interested in, Sierra Bicycle Group.

For each correct customer entry the database engine finds in the index, it looks up the matching order in the table according to the pointer in the second column of the index. Only the orders for the correct customer are read. Finally, since information in the index is organized according to the customer names, the database engine does not need to continue searching through the index or the table as soon as it finds an index entry that does not match the requested customer.

The advantage of this highly organized search through a database table according to an index is speed. Using indexes speeds up data retrieval and report generation, important factors when reporting on large database files.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com